﻿// =================================================================== 
// 项目说明
//====================================================================
// 文件： ERP_Sales.cs
// 创建时间：2016/12/3
// 负责人：
// ===================================================================
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using CRMMis.Model;
using CRMMis.IDAL;


namespace MSSQLDAL
{
    /// <summary>
    /// 数据层实例化接口类  销售单表.
    /// </summary>
    public partial class ERP_SalesDAL : IERP_SalesDAL
    {
		//此处写查询数据的sql语句，


        //if QueryDataString==""，DAL层的GetList，GetRecordCount等函数将直接存取ERP_Sales表


        //if QueryDataString!=""，则DAL层的GetList，GetRecordCount等函数将存取该SQL语句，取代表
		//注意：所写sql语句执行结果的字段必须是唯一的

		private const string QueryDataString = @"SELECT a.*,b.CustomerName,c.USER_NAME as InUser_Name,
                d.USER_NAME as checkName FROM ERP_Sales a
                LEFT JOIN ERP_Customer b ON a.CustomId=b.CustomerId
                LEFT JOIN SYS_USER c ON a.InUser= c.USER_ID
                LEFT JOIN SYS_USER d ON a.CheckUser= d.USER_ID";

        private const string BaseString = @"select SQLA.*,
            A.CustomerName,
            B.USER_NAME SALER,
            C.Supplyprice,(SQLA.Count*C.Supplyprice) SumPrice,
            D.CargoName,D.CargoCode,D.Spec,D.Area,D.Version,D.ProduceName,E.BatchNo from(
			select A.SalesNo as BUSINESSNO,
            A.CustomId,
            A.StoreRoomId Owner,
            A.InDate,
            B.ItemId,
            B.Count,
            1 as BUSINESSTYPE,
            A.State 
            from ERP_Sales A LEFT JOIN 
            ERP_SalesDetail B ON A.SalesId=B.SalesId
            union
            select A.PerReturnNo as BUSINESSNO,
            A.CustomerId CustomId,
            A.Owner,
            A.InDate,
            B.ItemId,
            B.Count,
            2 as BUSINESSTYPE,
            A.State 
            from ERP_PerReturn A LEFT JOIN 
            ERP_PerReturnDetail B ON A.PerReturnId=B.PerReturnId
            ) SQLA LEFT JOIN ERP_Customer A on SQLA.CustomId=A.CustomerId
            LEFT JOIN SYS_USER B ON SQLA.Owner=B.USER_ID
            LEFT JOIN ERP_PersonStockDetail E ON SQLA.ItemId=E.ItemId
            LEFT JOIN ERP_PersonStock F ON E.StoreId=F.StoreId
            LEFT JOIN ERP_Cargo_Detail C ON F.DetailId=C.DetailId
            LEFT JOIN ERP_Cargo D ON C.CargoId=D.CargoId";

        public DataSet GetBussness(int iPageSize, int iPageIndex, string sWhere)
        {
            string sOder = "CargoName,Spec,Version,Area,ProduceName,BatchNo,BUSINESSNO,BUSINESSTYPE";

            string TableString2 = @"SELECT * FROM 
                (" + BaseString + ") SQLA1 ";

            if (sWhere.Length > 0)
                TableString2 += " where " + sWhere + "";

            TableString2 = "(" + TableString2 + ") SQLA";

            string sqlStr = "select * from (select ROW_NUMBER() OVER(order by " + sOder + " ) as row, * from " + TableString2;

            sqlStr += ") SQL1 where row between " + Convert.ToString(iPageSize * (iPageIndex - 1) + 1) + " and " + Convert.ToString(iPageSize * iPageIndex);

            WriteLog("ERP_SalesDAL", "GetBussness(int iPageSize,int iPageIndex, string sWhere, string sOder)", sqlStr);

            return SqlHelper.ExecuteDataset(Conn.SqlConnStr, CommandType.Text, sqlStr);
        }

        public int GetBussness(string sWhere)
        {
            string strql = "select count(*) from (select * from ( " + BaseString + ") sqll where " + sWhere + ") SQLA1";

            WriteLog("ERP_SalesDAL", "GetBussness(string sWhere)", strql);

            return Convert.ToInt32(SqlHelper.ExecuteScalar(Conn.SqlConnStr, CommandType.Text, strql));
        }

    }
}
